/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBM;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 *
 * @author CKCantillas
 */
public class DBManager {
    
    /* 
     * 
     * Return type: boolean
     * Returns TRUE if registered Member's ID and password match.
     * Returns FALSE if Member is not in the database, or password does not match.
     */
    public boolean logIn(String memberID, String password) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            ResultSet rs = null;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * " +
                    "FROM member " +
                    "WHERE IDnumber = ? AND password = ?");
            
            int i = 1;
            
            ps.setString(i++, memberID);
            ps.setString(i++, password);
            
            rs = ps.executeQuery();
            
            if (rs.next()) {
                return true;
            }
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Checks the database if a member with memberID is already logged in
     * to the residency by searching the logged_in_members table for a
     * row with member_IDnumber.
     * 
     * Returns TRUE if a row with memberID exists.
     * Retursn FALSE if a row with memberID does not exist.
     */
    public boolean isLoggedInResidency(String memberID) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs = null;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * " + 
                    "FROM logged_in_members " +
                    "WHERE member_IDnumber = " + memberID);
            
            rs = ps.executeQuery();
            
            if (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Adds a new row to residency_log table.
     * 
     * Return type: boolean
     * Returns TRUE if a new row is successfully added to the residency_log table.
     * Returns FALSE if no row is added to the residency_log table.
     */
    public boolean addResidencyLog(Objects.ResidencyLog newResLog) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO residency_log(staffer_id, date, login) " +
                    "values(?, ?, ?)");
            
            int i = 1;
            
            ps.setString(i++, newResLog.getMemberID());
            ps.setString(i++, newResLog.getDate());
            ps.setString(i++, newResLog.getTimeIn());
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Adds a row with resID and memID to logged_in_members table.
     * 
     * Return type: boolean
     * Returns TRUE if a row is successfully added to logged_in_members table.
     * Returns FALSE if a row is not added to logged_in_members table.
     */
    public boolean addLoggedInMember(int resID, String memID) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO logged_in_members " +
                    "values(?, ?)");
            
            int i = 1;
            
            ps.setInt(i++, resID);
            //ps.setString(i++, Integer.toString(resID));
            ps.setString(i++, memID);
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Gets the residency_log_id of the row with memberID
     * from table logged_in_members.
     *
     * Return type: int
     * Returns the residency_log_residency_log_id of the row with memberID.
     */
    public int getResidencyLoggedInID(String memberID) {
        int id = -1;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT residency_log_residency_log_id " +
                    "FROM logged_in_members " +
                    "WHERE member_IDnumber = ?");
            
            int i = 1;
            ps.setString(i++, memberID);
            
            rs = ps.executeQuery();
            
            rs.next();
            id = rs.getInt("residency_log_residency_log_id");
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return id;
    }
    
    /* Gets the residency_log_id of the row with memberID, date, and timeIn
     * from table residency_log.
     * 
     * Return type: int
     * Returns the residency_log_id of the row with memberID, date, and timeIn.
     */
    public int getResidencyLogID(Objects.ResidencyLog rl) {
        int id = -1;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT residency_log_id " +
                    "FROM residency_log " +
                    "WHERE staffer_id = ? AND date = ? AND login = ?");
            
            int i = 1;
            
            ps.setString(i++, rl.getMemberID());
            ps.setString(i++, rl.getDate());
            ps.setString(i++, rl.getTimeIn());
            
            rs = ps.executeQuery();
            
            rs.next();
            id = rs.getInt("residency_log_id");
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return id;
    }
    
    /* Updates the logout column of a row with logID in table residency_log.
     * 
     * Return type: boolean
     * Returns TRUE if target row was successfully updated.
     * Returns FALSE if target row was not updated.
     */
    public boolean updateResidencyLog(int logID, String outTime) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "UPDATE residency_log " +
                    "SET logout = ? " +
                    "WHERE residency_log_id = ?");
            
            int i = 1;
            
            ps.setString(i++, outTime);
            ps.setString(i++, Integer.toString(logID));
            
            ps.executeUpdate();
            
            return true;
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Deletes row with resLogID from logged_in_members table.
     *
     * Return type: boolean
     * Returns TRUE if row is successfully deleted.
     * Returns FALSE if row is not deleted.
     */
    public boolean removeLoggedInMember(int resLogID) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "DELETE FROM logged_in_members " +
                    "WHERE residency_log_residency_log_id = ?");
            
            int i = 1;
            
            ps.setString(i++, Integer.toString(resLogID));
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.out.println(e);
        }
        
        return false;
    }
    
    /* Gets the residency logs of Member with memberID.
     * 
     * Return type: ResultSet
     * Returns the ResultSet containing the residency logs of Member with memberID.
     */
    public ResultSet getResidencyLog(String memberID) {
        ResultSet rs = null;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * " +
                    "FROM residency_log " +
                    "WHERE member_IDnumber = ?");
            
            int i = 1;
            
            ps.setString(i++, memberID);
            
            rs = ps.executeQuery();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return rs;
    }
    
    /* Gets all residency logs.
     * 
     * Return type: ResultSet
     * Returns the ResulSet containing all residency logs.
     */
    public ResultSet getResidencyLog() {
        ResultSet rs = null;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT IDnumber, last_name, residency_log_id, date, login, logout" +
                    "FROM member, residency_log" +
                    "WHERE IDnumber = staffer_id");
            
            rs = ps.executeQuery();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return rs;
    }
    
    /* Gets the list of members logged in to the residency
     * by querying the logged_in_members table. The result 
     * is arranged in ascending order.
     * 
     * Return type: ResultSet
     * The ResultSet contains the ID numbers of the members in the table
     * in ascending order.
     */
    public ResultSet getLoggedInMembersList() {
        ResultSet rs = null;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT member_IDnumber " +
                    "FROM logged_in_members " +
                    "ORDER BY member_IDnumber ASC");
            
            rs = ps.executeQuery();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return rs;
    }
    
    /* Checks if book with isbn exists.
     * 
     * Return type: boolean
     * Returns TRUE if Book with isbn exists.
     * Returns FALSE if Book with isbn does not exist.
     */
    public boolean bookExists(String isbn) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT isbn FROM book " +
                    "where isbn = ?");
            
            int i = 1;
            
            ps.setString(i++, isbn);
            
            rs = ps.executeQuery();
            
            if (rs != null)
                return true;
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Gets all rows from the book table based on a certain filter. 
     * The book object passed only has one attribute that is not null, which
     * serves as the filter for the search.
     * 
     * Return type: ResultSet
     * Returns a ResultSet containing all rows from the book table containing
     * a certain filter.
     */
    public ResultSet searchBook(Objects.Book book) {
        ResultSet rs = null;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps;
            
            if (book.getIsbn() != null && book.getIsbn().length() > 0) {
                ps = conn.prepareStatement(
                        "SELECT * " +
                        "FROM book " +
                        "WHERE isbn = " + book.getIsbn());
                
                rs = ps.executeQuery();
            }
            else if (book.getTitle() != null && book.getTitle().length() > 0) {
                ps = conn.prepareStatement(
                        "SELECT * " +
                        "FROM book " +
                        "WHERE title = " + book.getTitle());
                
                rs = ps.executeQuery();
            }
            else if (book.getAuthor() != null && book.getAuthor().length() > 0) {
                ps = conn.prepareStatement(
                        "SELECT * " +
                        "FROM book " +
                        "WHERE author = " + book.getAuthor());
                
                rs = ps.executeQuery();
            }
            else if (book.getGenre() != null && book.getGenre().length() > 0) {
                ps = conn.prepareStatement(
                        "SELECT * " +
                        "FROM book " +
                        "WHERE genre = " + book.getGenre());
                
                rs = ps.executeQuery();
            }
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return rs;
    }
    
    /* Return type: boolean
     *
     * Returns TRUE if newBook is successfully added.
     * Returns FALSE if newBook is not added.
     * 
     */
    public boolean addBook(Objects.Book newBook) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO book " +
                    "values(?, ?, ?, ?)");
            
            int i = 1;
            
            ps.setString(i++, newBook.getIsbn());
            ps.setString(i++, newBook.getTitle());
            ps.setString(i++, newBook.getAuthor());
            ps.setString(i++, newBook.getGenre());
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /*
     * 
     */
    public boolean updateBook(Objects.Book updatedBook) {
        
        
        return false;
    }
    
    /* Return type: boolean
     * 
     * Returns TRUE if a book with isbn is deleted.
     * Returns FALSE if no book with isbn is deleted.
     */
    public boolean deleteBook(String isbn) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "DELETE FROM book " +
                    "WHERE isbn = ?");
            
            int i = 1;
            
            ps.setString(i++, isbn);
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Return type: boolean
     * 
     * Returns TRUE if (a) book/s with title and author is/are deleted.
     * Returns FALSE if no book with title and author is deleted.
     */
    public boolean deleteBook(String title, String author) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "DELETE FROM book " +
                    "WHERE title = ? AND author = ?");
            
            int i = 1;
            
            ps.setString(i++, title);
            ps.setString(i++, author);
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Creates a new entry in the table member_has_book.
     * 
     * Return type: boolean
     * Returns TRUE if a row has been successfully inserted into table member_has_book.
     * Returns FALSE if no row has been inserted into table member_has_book.
     */
    public boolean borrowBook(String isbn, String memberID, String date) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO member_has_book(member_IDnumber, book_isbn, date_borrowed) " +
                    "values(?, ?, ?)");
            
            int i = 1;
            
            ps.setString(i++, memberID);
            ps.setString(i++, isbn);
            ps.setString(i++, date);
            
            ps.executeUpdate();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Gets a ResulSet containing the list of borrowed and unreturned books of
     * Member with memberID.
     * 
     * Return type: ResultSet
     * Returns a ResulSet containing rows with the title, author, genre, and
     * date borrowed of books that were borrowed and not yet returned.
     */
    public ResultSet getBorrowedBooks(String memberID) {
        ResultSet rs = null;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT title, author, genre, date_borrowed " +
                    "FROM member_has_book, book " +
                    "WHERE member_IDnumber = ? AND book_isbn = isbn " +
                            "AND date_returned IS NULL");
            
            int i = 1;
            
            ps.setString(i++, memberID);
            
            rs = ps.executeQuery();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return rs;
    }
    
    /* 
     * 
     * Return type: int
     * Returns the borrow_id of row with isbn, memberID and dateBorrowed.
     * Returns -1 if row does not exist.
     */
    public int getBorrowID(String isbn, String memberID, String dateBorrowed) {
        int id = -1;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT borrow_id " + 
                    "FROM member_has_book " + 
                    "WHERE member_IDnumber = ? " + 
                        "AND book_isbn = ? " + 
                        "AND date_borrowed = ?");
            
            int i = 1;
            
            ps.setString(i++, memberID);
            ps.setString(i++, isbn);
            ps.setString(i++, dateBorrowed);
            
            rs = ps.executeQuery();
            
            rs.next();
            
            id = rs.getInt("borrow_id");
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return id;
    }
    
    /* Updates the date_returned column of the row with borrow_id.
     * 
     * Return type: boolean
     * Returns TRUE if row is successfully updated.
     * Returns FALSE if row is not updated.
     */
    public boolean returnBook(int borrowID, String date) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO member_has_book(date_returned) " +
                    "values(?) " +
                    "WHERE borrow_id = ?");
            
            int i = 1;
            
            ps.setString(i++, date);
            ps.setString(i++, Integer.toString(borrowID));
            
            ps.executeUpdate();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Get all the library logs.
     * 
     * Return type: ResultSet
     * Returns a ResultSet containing all library logs.
     */
    public ResultSet getLibraryLog() {
        ResultSet rs = null;
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT borrow_id, member_IDnumber, book_isbn, date_borrowed, " +
                        "date_returned, last_name, title, author " +
                    "FROM member, book, member_has_book " +
                    "WHERE book_isbn = isbn AND member_IDnumber = IDnumber");
            
            rs = ps.executeQuery();
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return rs;
    }
    
    /*
     * Return type: boolean
     * Returns TRUE if member exists in the database.
     * Returns FALSE if member does not exist in the database.
     */
    public boolean memberExists(Objects.Member member) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * " +
                    "FROM member " +
                    "WHERE IDnumber = " + member.getIdNo());
            
            rs = ps.executeQuery();
            
            if (rs.next())
                return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /* Adds row to member table.
     * 
     * Return type: boolean
     * Returns TRUE if newMember is added successfully.
     * Returns FALSE if newMember is not added.
     */
    public boolean addMember(Objects.Member newMember) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO member " +
                    "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            int i = 1;
            
            ps.setString(i++, newMember.getIdNo());
            ps.setString(i++, newMember.getPassword());
            ps.setString(i++, newMember.getLastName());
            ps.setString(i++, newMember.getFirstName());
            ps.setString(i++, newMember.getMiddleName());
            ps.setString(i++, newMember.getCollege());
            ps.setString(i++, newMember.getCourse());
            ps.setString(i++, newMember.getDateOfBirth());
            ps.setString(i++, newMember.getStatus());
            ps.setString(i++, newMember.getSection());
            ps.setString(i++, newMember.getContactNo());
            ps.setString(i++, newMember.getEmailAd());
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
    
    /**/
    public Objects.Member getMember(String id) {
        Objects.Member mem = new Objects.Member();
        
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * " + 
                    "FROM member " +
                    "WHERE IDnumber = " + id);
            
            rs = ps.executeQuery();
            
            while (rs.next()) {
                mem.setIdNo(rs.getString("IDnumber"));
                mem.setPassword(rs.getString("Password"));
                mem.setLastName(rs.getString("last_name"));
                mem.setFirstName(rs.getString("first_name"));
                mem.setMiddleName(rs.getString("middle_name"));
                mem.setCollege(rs.getString("college"));
                mem.setCourse(rs.getString("degree_program"));
                mem.setDateOfBirth(rs.getString("date_of_birth"));
                mem.setStatus(rs.getString("status"));
                mem.setSection(rs.getString("Section_section_name"));
                mem.setContactNo(rs.getString("contact_number"));
                mem.setEmailAd(rs.getString("email_address"));
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return mem;
    }
    
    /* Checks if member with id is an editor
     * 
     * Return type: boolean
     * Returns TRUE if member is an editor
     * Returns FALSE if member is not an editor
     */
    public boolean isEditor(String id) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            ResultSet rs;
            
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * " + 
                    "FROM editor " +
                    "WHERE member_IDnumber = " + id);
            
            rs = ps.executeQuery();
            
            if (rs.next()) 
                return true;
            
        } catch (Exception e) {
            System.err.print(e);
        }
        
        return false;
    }
    
    /**/
    public boolean updateMember(Objects.Member mem) {
        try {
            DBConnection dbc = new DBConnection();
            Connection conn = dbc.getConnection();
            
            PreparedStatement ps = conn.prepareStatement(
                    "UPDATE member " +
                    "SET last_name = ?, first_name = ?, middle_name = ?, college = ?, " +
                    "degree_program = ?, contact_number = ?, email_address = ?, " +
                    "password = ? " + //dob
                    "WHERE IDnumber = ?");
            
            int i = 1;
            
            ps.setString(i++, mem.getLastName());
            ps.setString(i++, mem.getFirstName());
            ps.setString(i++, mem.getMiddleName());
            ps.setString(i++, mem.getCollege());
            ps.setString(i++, mem.getCourse());
            //ps.setString(i++, mem.getDateOfBirth());
            ps.setString(i++, mem.getContactNo());
            ps.setString(i++, mem.getEmailAd());
            ps.setString(i++, mem.getPassword());
            ps.setString(i++, mem.getIdNo());
            
            ps.executeUpdate();
            
            return true;
            
        } catch (Exception e) {
            System.err.println(e);
        }
        
        return false;
    }
}